from flask import Blueprint, request, current_app, send_file
from collections import defaultdict
from utilsapp.mysql_con import  db_delete, db_insert,db_list, db_update, db_insert_id
from utilsapp.mysql_con import db_pagelist,db_deletes
from utilsapp.common import assign_value, parsing_data_id
from datetime import datetime
from utilsapp import utils
import time, os, uuid, traceback, pandas, json, requests
from openpyxl import load_workbook
from pypinyin import pinyin, Style
import paho.mqtt.publish as publish
energy = Blueprint('energy', __name__)


# 查询诊断报告
@energy.route('/getlist', methods=['POST'])
def getlist():
    a = request.json
    scope_id = assign_value(a, "scope_id", 0)
    scoped = parsing_data_id(scope_id, request)
    name = assign_value(a, "name")
    query = ""
    # if scoped != "":
        # query += f" and {scoped}"
    if name != "":
        query += f" and name like '%{name}%'"
    sql = f"select * from energy where 1=1 {query} ORDER BY id DESC"

    res = db_pagelist(sql, a['page'], a['size'])
    return res

@energy.route('/add', methods=['post'])
def add():
    return db_insert('energy', request.json)

@energy.route('delete', methods=['post'])
def delete():
    id_ = request.json['id']
    return db_delete("energy", f"where id = %s", {'mid': id_})
@energy.route('deletes', methods=['post'])
def deletes():
    id_ = ' or '.join([('id = ' + str(item)) for item in request.json['idList']])
    sql = f"DELETE FROM energy where {id_}"
    return db_deletes(sql)


@energy.route('/edit', methods=["post"])
def edit():
    id_ = assign_value(request.json, "id")
    keys_not_add = ['id']
    addModel = {key: request.json[key]
                    for key in request.json if key not in keys_not_add}
    return db_update('energy',addModel,f"where id =%s", {"id_": id_})

@energy.route("/download",methods=['post'])
def download():
    date = time.strftime("%Y%m%d", time.localtime())
    filename = request.json['filename']
    sql = f"select * from energy ORDER BY id DESC"
    data = db_list(sql)['data']
    res_ = []
    for i in data:
        level = json.loads(i['level'])
        i['level1'] = level[0]['level1']
        i['level2'] = level[0]['level2']
        i['level3'] = level[0]['level3']
        its1 = ['序号','项目名称','能耗单位','一级能耗指标','二级能耗指标','三级能耗指标','当月用电量','当月抽出风量/平均全压','当月排水量/排水垂直高度','当月提升量/可比高度','当月压风量/平均绝对压力','当月数值','当月结果','累计数值','累计结果']
        its2 = ['id','name','unit','level1','level2','level3','power','wind','water','up','air','month_data','month_res','accumulate_data','accumulate_res']     
        d = {}
        for index in range(len(its1)):
                d[its1[index]] = i[its2[index]] 
        res_.append(d)
    df = pandas.DataFrame(res_)
    script_dir = os.path.dirname(os.path.abspath(__name__))
    filePath = script_dir + '/static/upload/'+date
    if not os.path.exists(filePath):
        os.mkdir(filePath)
    filePath+=f"/{filename}"
    df.to_excel(filePath, index=False)
    workbook = load_workbook(filePath)
    worksheet = workbook.active
    for column in worksheet.columns:
        worksheet.column_dimensions[column[0].column_letter].width = 14
    worksheet.column_dimensions['B'].width = 20
    worksheet.column_dimensions['H'].width = 24
    worksheet.column_dimensions['I'].width = 24
    worksheet.column_dimensions['J'].width = 24
    worksheet.column_dimensions['K'].width = 24
    workbook.save(filePath)
    return send_file(filePath, as_attachment=True, attachment_filename=filename)